a. How Do I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
a. How Do I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
Use DatabasesUse ViewsUse DocumentsSearch for InformationUse MailPrint Documents and ViewsKeep Notes SecureShare Information With Other ApplicationsUse Notes Away from a NetworkDesign Notes DatabasesManage Notes DatabasesAdminister Notes Servers---Where is this Release 3 command?What are the important new features?What changed at the last minute?How do I upgrade my site to Release 4?----Things you do before startingThings you do every dayThings you do frequentlyOther things-----About NotesSetting Up NotesStarting & Stopping NotesAbout Other Notes Documentation------About MenusAbout the SmartIcons PaletteAbout WindowsAbout FoldersAbout Split PanesAbout the Status Bar-------SmartIcon ShortcutsMouse ShortcutsKeyboard Shortcuts--------About ScriptingLotusScript BasicsLotusScript Reference----------@Functions Basics@Functions Reference@Commands Basics@Comands Reference---------About MessagesAbout Customer SupportList of Messagest
H_ERROR_MESSAGES_OVERVIEW_MIDTOPIC_73957251229232354=Errors reported by NotesSQLH_ERROR_MESSAGES_OVERVIEW_MIDTOPIC_73957251229232355=Errors reported by Notes
OverviewContext Help FormWhat is NotesSQL?Overview
CN=Michael Stewart/OU=CAM/O=Lotus
#############
H_INSTALLING_NOTESSQL_OVERVIEWInstalling NotesSQL
02 Chapter 2: Installation and Configuration
Driver, adding and deletingInstallation
Contents
OverviewContext Help FormInstalling NotesSQLOverview
H_INSTALLING_NOTESSQL_OVERVIEW_MIDTOPIC_368837628829232348=All 32-bit Windows platforms (Windows 95 or NT for Intel or Alpha)H_INSTALLING_NOTESSQL_OVERVIEW_MIDTOPIC_368837628829232349=16-bit Windows
H_INSERT_OVERVIEW_MIDTOPIC_115364224029232561=SyntaxH_INSERT_OVERVIEW_MIDTOPIC_115364224029232562=ExamplesH_INSERT_OVERVIEW_MIDTOPIC_115364224029232563=ArgumentsH_INSERT_OVERVIEW_MIDTOPIC_115364224029232564=Notes Field Events
OverviewContext Help FormMulti-Valued Fields (List Fields)Overview
H_MULTIVALUED_FIELDS_LIST_FIELDS_OVERVIEW_MIDTOPIC_115364224029232561=Text Form FieldsH_MULTIVALUED_FIELDS_LIST_FIELDS_OVERVIEW_MIDTOPIC_115364224029232562=Numeric and Date Form FieldsH_MULTIVALUED_FIELDS_LIST_FIELDS_OVERVIEW_MIDTOPIC_78295372829252458=Display separator characters in view columns
OverviewContext Help FormPredicate OperatorsOverview
CN=Michael Stewart/OU=CAM/O=Lotus
H_DATA_TYPES_OVERVIEWData Types
03 Chapter 3: Using NotesSQL
Data typesList fieldsRich text fields
Contents
OverviewContext Help FormData TypesOverview
H_DATA_TYPES_OVERVIEW_MIDTOPIC_73957251229232354=ODBC SQL to Notes data type mapping:H_DATA_TYPES_OVERVIEW_MIDTOPIC_73957251229232355=Notes to SQL data type mapping:
H_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEWConfiguring a Lotus Notes Data Source
02 Chapter 2: Installation and Configuration
Data sourceDatabase connection parametersInstallationSQL statement length
Contents
OverviewContext Help FormConfiguring a Lotus Notes Data SourceOverview
H_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232348=Data Source Name *H_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232349=DescriptionH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232350=Server *H_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232351=Database *H_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232352=Max Length of Text FieldsH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232353=Max Number of TablesH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232354=Max Number of SubqueriesH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232355=Keep Temporary Indexes until SQLDisconnectH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232356=Return Notes Implicit FieldsH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232357=Map Special CharactersH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232358=Max Length of Rich Text FieldsH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232359=SQL Statement LengthH_CONFIGURING_A_LOTUS_NOTES_DATA_SOURCE_OVERVIEW_MIDTOPIC_368837628829232360=Thread Timeout (available on 32-bit Windows platforms only)
The three most important things you need to know about
this guide
are:
To find an entry in the Index view, click the view pane, type as many letters as you want of the entry you're looking for, and click OK. Click the green triangle next to any index entry to expand its documents.
To jump from one topic to another, click green
underlined
text (click green text in a box for a pop-up definition of a term). To return to where you were, click Go Back. To return to the Contents screen, click Help Topics.
To search for a word or phrase, click Search in the navigation pane and follow the on-screen instructions. Notes displays a list of all the topics that contain the word or phrase, in order of the most occurrences.
About editing topics
To suit the needs of your site, you can create a replica of this database and edit its documents strictly for the sole use of users within your organization. You cannot resell or otherwise distribute this documentation, modified or unmodified, to anyone outside your organization. Lotus assumes no responsibility for the technical accuracy of any modifications made to this documentation or the operation of Lotus products in reliance on such modifications.
To permit editing of topics in
this guide
1. Select the
guide database icon and choose View - Design.
2. Select Forms, then open the Context Help form.
3. Select the QueryOpen event in the design pane. REM all the lines in the script.
4. Select the QueryModeChange event. REM all the lines in the script.
5. Save the form.
Notes Product Development
)I^-.
PURSAF
O=Lotus
O=Lotus
PURSAFO
O=Lotus
OU=CAM/O=Lotus
PURSAFO
OU=CAM/O=Lotus
CN=Michael Stewart/OU=CAM/O=Lotus
A]yvinNj
PURSAFO
$Info
$Body
&Arial
About the Lotus NotesSQL guide
guide
is a Notes database that describes how to use Lotus NotesSQL. It provides the following information:
Overview of ODBC and NotesSQL
Instructions for installing and using NotesSQL
General advice about creating programs that use NotesSQL
Information about SQL statements supported by NotesSQL
Choose Help - Using This Database for information on using the
guide.
Credit
Lotus NotesSQL is developed by Lotus Development Corporation.
Copyright
Copyright
Revision history
Original Original file produced for Lotus NotesSQL 2.0.
First revision Incorporates information on Lotus NotesSQL 2.01.
Second revision Incorporates information on Lotus NotesSQL 2.02.
Third revision Incorporates information on Lotus NotesSQL 2.03.
Fourth revision Incorporates information on Lotus NotesSQL 2.04.
Fifth revision Incorporates information on Lotus NotesSQL 2.05.
Under the copyright laws, neither the documentation nor the software may be copied, photocopied, reproduced, translated, or reduced to any electronic medium or machine-readable form, in whole or in part, without the prior written consent of Lotus Development Corporation, except in the manner described in the software agreement.
Copyright 1996 - 1999
Lotus Development Corporation
55 Cambridge Parkway
Cambridge, MA 02142
All rights reserved. Published in the United States.
This software is subject to the Lotus NotesSQL License Agreement.
Disclaimer
This database contains descriptions of the software in Lotus NotesSQL. This information will change in both content and appearance in future releases of Lotus NotesSQL and does not represent a commitment on the part of Lotus Development Corporation.
The sample programs are not warranted to perform any function other than providing a simple demonstration of how to make a particular call in a program context.
THIS INFORMATION AND ALL OTHER DOCUMENTATION (IN PRINTED OR ELECTRONIC FORM) ARE PROVIDED FOR REFERENCE PURPOSES ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THIS INFORMATION, THIS INFORMATION AND ALL OTHER DOCUMENTATION ARE PROVIDED "AS IS" WITHOUT ANY WARRANTY WHATSOEVER AND TO THE MAXIMUM EXTENT PERMITTED, LOTUS DEVELOPMENT CORPORATION DISCLAIMS ALL WARRANTIES, INCLUDING WITHOUT LIMITATION THE IMPLIED WARRANTIES OF MERCHANTABILITY, NONINFRINGEMENT AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE SAME. LOTUS DEVELOPMENT CORPORATION SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES, INCLUDING WITHOUT LIMITATION, DIRECT, INDIRECT, CONSEQUENTIAL OR INCIDENTAL DAMAGES, ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS INFORMATION OR ANY OTHER DOCUMENTATION. NOTWITHSTANDING ANYTHING TO THE CONTRARY, NOTHING CONTAINED IN THIS INFORMATION OR ANY OTHER DOCUMENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR REPRESENTATIONS FROM LOTUS DEVELOPMENT CORPORATION (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE AGREEMENT GOVERNING THE USE OF THIS SOFTWARE.
List of trademarks
Trademarks
Domino, NotesSQL, and Notes are trademarks and Lotus, Lotus Notes, and 1-2-3 are registered trademarks of Lotus Development Corporation. IBM and OS/2 are registered trademarks of International Business Machines Corporation.
Other product and company names mentioned herein may be the trademarks or registered trademarks of their respective owners.
Portions of the software are copyright 1994, 1995 Casahl Technology, Inc. All Rights Reserved.
Notes Product Development
)I^-.
PURSAF
O=Lotus
O=Lotus
PURSAFO
O=Lotus
OU=CAM/O=Lotus
PURSAFO
OU=CAM/O=Lotus
CN=Michael Stewart/OU=CAM/O=Lotus
A]yvinNj
PURSAFO
$Info
$Body
Categori_zeL
_Edit Document
Send Docu_ment
_Forward
_Move To Folder...
_Remove From Folder
Notes Product Development
)I^-.
PURSAF
O=Lotus
O=Lotus
PURSAFO
O=Lotus
OU=CAM/O=Lotus
PURSAFO
OU=CAM/O=Lotus
CN=Michael Stewart/OU=CAM/O=Lotus
A]yvinNj
PURSAFO
$TITLE
$Name
$Index
$Formula
$FormulaClass
$ViewFormat
$Comment
$ACTIONS
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
$TITLE
$Index
$Formula
$FormulaClass
$ViewFormat
$FormFormula
$Comment
"Arial
Subject
Lotus NotesSQL Reference |
Copyright 1999 Lotus Development Corporation
| - &P - |
Context Help Form'++LotusScript Development Environment:2:5:(Options):0:66
'++LotusScript Development Environment:2:5:(Forward):0:1
Declare Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
Declare Sub Querymodechange(Source As Notesuidocument, Continue As Variant)
'++LotusScript Development Environment:2:5:(Declarations):0:2
'++LotusScript Development Environment:2:2:BindEvents:1:129
Private Sub BindEvents(Byval Objectname_ As String)
Static Source As NOTESUIDOCUMENT
Set Source = Bind(Objectname_)
On Event Queryopen From Source Call Queryopen
On Event Querymodechange From Source Call Querymodechange
End Sub
'++LotusScript Development Environment:2:2:Queryopen:1:12
Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
End Sub
'++LotusScript Development Environment:2:2:Querymodechange:1:12
Sub Querymodechange(Source As Notesuidocument, Continue As Variant)
To respond to Lotus with your comments, you must have a connection to the World Wide Web and a browser specified in your Location document. Check with your server administrator for information on your Internet connection, and look in Notes Help for information on how to configure your Notes workstation to connect to the Web.
What entries should the document appear under in the Index view?
Index_Entries
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
|`|4@U
$TITLE
$INFO
$$ScriptName
$BODY
Show Navigator.
0S0E
Expand All
0S0E
Collapse All
0S0E
Next.
Domino
ViewNextPage
$$WebClient
0S0E
Previous2
Domino
ViewPreviousPage
$$WebClient
0S0E
Search
0S0E
$$WebClient
0S0E
Categori_ze
_Edit Document
Send Docu_ment
_Forward
_Move To Folder...
_Remove From Folder
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
$Author
$Index
$Formula
$FormulaClass
$ViewFormat
$Comment
$ACTIONS
$TITLE
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
Lotus NotesSQL
Reference
describes how to install, configure, and use NotesSQL(TM), the Lotus Notes
ODBC driver, to open, query, and modify Lotus
Notes
v databases.
329696435229232314
Organization
Chapter 1: About NotesSQL introduces NotesSQL.
Chapter 2: Installation and Configuration.
Chapter 3: Using NotesSQL describes how to use NotesSQL.
Chapter 4: Programming provides programming information for application programmers using the Open Database Connectivity (ODBC) application programming interface (API).
Appendix A: SQL Statements.
329696435229232315
Conventions Used in This BookT
The Lotus NotesSQL Reference uses the following conventions:
SQL code is in Courier 10 point. SQL keywords are in upper case. For example:
SELECT * FROM MainqViewqAll MainqViewqAllU
Note Introduces additional technical information about a command or procedure.
Section
Section
3S4S6S10S12S13S14S15S17S21S
MainComment
Reviewer"
0S0E
Indent
Subject
Indent
Subject
Subject
3S4S6S10S12S13S15S16S17S19S23S25S26S28S32S34S35S
Book$21Doc_Number$17$22$Conflict$REF$C1$
Reference
Viewt
L1S5S7S8S6
Book$21Doc_Number$22
Using Notes Internal Fields
New Features in this Release
What is ODBC?"
Adding a Lotus Notes Data Source
How to Use This Guide
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
View Column Definitions
Software Requirements
Error Messages
What is NotesSQL?
Installing NotesSQL
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
The Universal Relation
Rich Text Fields
CREATE INDEX
CREATE TABLE
Section
Section
3S4S6S10S12S13S14S15S17S21S
Indent
Subject
Indent
Subject
Subject
3S4S6S10S12S13S15S16S17S19S23S25S26S28S32S34S35S
Book$16Doc_Number$20$Conflict$REF$C1$
Troubleshooting
Viewt
L1S5S7S8S+
$16Doc_Number$20
Using Notes Internal Fields
New Features in this Release
What is ODBC?"
Adding a Lotus Notes Data Source
How to Use This Guide
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
View Column Definitions
Software Requirements
Error Messages
What is NotesSQL?
Installing NotesSQL
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
The Universal Relation
Rich Text Fields
CREATE INDEX
CREATE TABLE
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL recognizes Notes forms and views as tables. In addition, the driver recognizes the Universal Relation as a table. However, Notes forms and views have very different properties that affect the performance of data access and display with NotesSQL.
Views in Notes databases list documents in a specific order. Avoid selecting from a table based on a Notes view and then specifying a different sort order. When you specify a different sort order on an existing view, Notes creates a temporary table on your workstation and re-sorts the documents. Creating a large temporary table and sorting the documents in that table will take a long time.
Temporary table creation requires you to have read/write access to the database. If you have read-only access, you cannot perform an operation like SELECT with ORDER BY or GROUP BY unless there is an existing view you can use to support the operation.
To display information from a view in a different sort order, use a table based on a Notes form and create an index on the form using the order you want, or create a new view either in Notes or through ODBC.
Tables derived from Notes forms are not necessarily indexed for fast access. When selecting data from a table based on a Notes form, NotesSQL looks for a view that acts as an index on that table. If such a view is present, access to the table will be fast. If no such view is present, access to the table may be slower.
Depending on the SQL statement, NotesSQL may need to search the entire database and check every document to see if it belongs to the table, or generate a temporary index on the fly.
If you use a GROUP BY, JOIN, or WHERE clause, you may want to create a view to act as an index for the field in the clause. Otherwise, the query optimizer generates a temporary index to enhance the performance of the SQL statement.
For more information about using views as indexes, see "
Click here
Using Notes Views as Indexes
Click here
Click here
See examples
Click here
MainComment
Reviewer"
0S0E
Index_Entries$22Subject$Conflict$REF$C1$
TopicType
GuideMe
L1S2S4S5S6S7S(
Index_EntriesSubject
Using Notes Internal Fields
New Features in this Release
What is ODBC?"
Adding a Lotus Notes Data Source
How to Use This Guide
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
View Column Definitions
Software Requirements
Error Messages
What is NotesSQL?
Installing NotesSQL
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
The Universal Relation
Rich Text Fields
CREATE INDEX
CREATE TABLE
CREATE VIEW
Using the Universal Relation
DELETE Positioned
DELETE Searched
Using Notes Views as Indexes
DROP VIEW
INSERT
SELECT
Numeric Operators
HAVING
ORDER BY
UNION
WHERE
Predicate Operators
Data Types
ODBC API Functions
ALTER TABLE
DROP INDEX
DROP TABLE
UPDATE Searched
GROUP BY
Column Functions
FOR UPDATE
FROM"
Frequently Asked Questions (FAQ)
UPDATE Positioned
Web Button
This button works only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Search
0S0E
Web Button
Use this button only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
NWIDX
0S0E
$$WebClient
0S0E
Categori_ze
_Edit Document
Send Docu_ment
_Forward
_Move To Folder...
_Remove From Folder
Notes Product Development
)I^-.
PURSAF
O=Lotus
O=Lotus
PURSAFO
O=Lotus
OU=CAM/O=Lotus
PURSAFO
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL 2.05 provides the following enhancements:
You can now create and modify File Data Sources (32-bit Windows only).
You can now browse available servers and databases in the dialog boxes displayed when you create or update a data source (32-bit Windows only).
NotesSQL now supports the ODBC API SQLPrimaryKeys function.
NotesSQL now supports the REPLACE and IFNULL scalar functions.
Newly inserted document items now get their flags from the analogous CDFIELD flags in the form.
The AS keyword is now optional when aliasing column names. This was required by Impromptu 5.0.
The SQLDriverConnect dialog box now displays NotesSQL options so you can update them as needed.
SQLDriverConnect now returns more connection attributes. For more information about NotesSQL-specific attributes, see "
Click here
Connecting to a Lotus Notes Data Source
Click here
." Consult the ODBC API reference for the recommended size of the output buffer used by SQLDriverConnect to store the returned attribute string. If the returned string exceeds the buffer size, increase the size and call the function again.
NotesSQL now supports INNER JOINs. For more information, see the
Click here
Click here
clause documentation and "
Click here
Tips for Third-Party Applications and Caveats
Click here
You can now use Universal Naming Convention (UNC) file names when creating, configuring, or connecting to a Notes database data source. The database must be on a local server (that is, it must be on a server accessible through your network file system, not a Domino server). UNC file names for Notes databases have the form "\\<ServerName>\<ShareName>\<Directory1>\<Directory2>\<...>\<DatabaseName>.NSF".
For information about problems resolved in this release and known issues, see the readme.txt file provided with the toolkit.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
To use Notes data through ODBC, you must have:
NotesSQL, the Lotus Notes ODBC driver.
An ODBC Driver Manager version 3.0 or later (for 32-bit Windows, 2.0 for 16-bit Windows).
One of the following:
Microsoft Windows 3.1 or later, or
Microsoft Windows 95, or
Microsoft Windows 98, or
Microsoft Windows NT 4.0 or later (for Intel or Alpha CPUs)
One of the following:
Designer clients:
Lotus Notes (full client) 4.5x, or
Lotus Notes Designer for Domino 4.6x, or
Domino Designer 5.0
Lotus Desktop License:
Lotus Notes Desktop License 4.6x, or
Lotus Notes 5.0
Lotus Server:
Lotus Domino Mail Server 4.6x
Lotus Domino Application Server 4.6x
Lotus Domino R5 Mail Server
Lotus Domino R5 Application Server
For client-based third-party applications, NotesSQL and the Designer or Desktop software must be installed on your local machine. For server-based applications (for example, Microsoft Internet Information Server), NotesSQL and any one of the versions above must be installed on the server.
Notes database files can reside on a server. You do not need to have local copies of these files, but must have at least reader access to them through Notes.
For more information about Notes databases, refer to your Lotus Notes documentation.
401314073629253485
Third-Party Application Support
We have tested this version of NotesSQL with the following products:
NetObjects Fusion Version 4.0
Microsoft FrontPage 98, using Active Data Objects (ADO)
Microsoft Active Server Pages, using ADO, Personal Web Server, and IIS Server
Microsoft Visual InterDev 6.0, using ADO
Seagate Crystal Reports 7.0
Microsoft Access / Office 97
Microsoft Visual Basic 6.0, using ADO
Lotus 1-2-3 Millenium Edition, using the DQA addin
All 32-bit Windows platforms (Windows 95 or NT for Intel or Alpha)
The 32-bit version of this version of NotesSQL is automatically installed when you run the self-executing file for your platform from the NotesSQL Web page. You can also download the self-executing file and run it later, thereby keeping the self-executing file on your system for later reuse. If you need to re-install NotesSQL, follow the steps below.
1. Do one of the following:
If you downloaded the self-executing installation file for your platform when you first installed this version of NotesSQL, run the file (NotesSQL205Alpha.exe for Alpha, NotesSQL205w32.exe for Intel) and follow the prompts that appear.
If you ran the self-executing installation file from the NotesSQL page, get it from the NotesSQL page in the Products portion of the Lotus Web site (http://www.lotus.com). Then run it and follow the prompts that appear.
Earlier versions of NotesSQL used a different "label"--the name of the driver as it appears in the ODBC Data Source Administrator. If the installation program detects an earlier version of the NotesSQL driver, it updates the registry and any existing system or user Data Sources to use a common label, "Lotus NotesSQL Driver (*.nsf)". This does not affect your ability to use existing Data Sources. The only difference you may notice is that the name of the driver has changed slightly in the ODBC Data Source Administrator.
For more information about the updates performed by this installation, see the readme.txt file in your installation directory.
2. At the end of the installation process, the ODBC Data Source Administrator dialog box appears so you can add Lotus Notes Data Sources. If you are upgrading from a previous 2.x version of NotesSQL, you do not have to make any other changes to your Data Sources beyond those made by the installation program, as noted above. All your previously added Data Sources will work with this release without further modification.
3. The installation program optionally adds shortcuts to the documentation to the Start menu. If you choose not to add shortcuts and you use Notes to view this documentation, move the documentation database (NOTESSQL.NSF) to your Notes data directory for ease of access.
368837628829232349
16-bit Windows
1. t
Run SETUP.EXE, which loads Microsoft ODBC Setup, and follow the prompts.
2. When the Install Drivers dialog box appears, select the NotesSQL driver and click OK. (You do not need to modify any information in the Advanced dialog box.)
3. At the end of the installation process, the ODBC Data Source Administrator dialog box appears so you can add Lotus Notes Data Sources. If you are upgrading from a previous 2.x version of NotesSQL, note that you do not have to make any changes to your Data Sources. All your previously added Data Sources will work with this release.
4. If you use Notes to view this documentation, move the documentation database (NOTESSQL.NSF) to your Notes data directory for ease of access.
See related topics
Click here
Adding a Lotus Notes Data Source
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The following ODBC SQL grammar is supported:
115304224029232561
Supported Statements
Click here
ALTER TABLE
Click here
Click here
CREATE INDEX
Click here
Click here
CREATE TABLE
Click here
Click here
CREATE VIEW
Click here
Click here
DELETE Positioned
Click here
Click here
DELETE Searched
Click here
Click here
DROP INDEX
Click here
Click here
DROP TABLE
Click here
Click here
DROP VIEW
Click here
Click here
INSERT
Click here
Click here
SELECT
Click here
Click here
FOR UPDATE
Click here
Click here
Click here
Click here
GROUP BY
Click here
Click here
HAVING
Click here
Click here
ORDER BY
Click here
Click here
UNION
Click here
Click here
UPDATE Searched
Click here
Click here
UPDATE Positioned
Click here
Click here
WHERE
Click here
115304224029232562
Supported Expressions, Functions, and Operatorsi
Click here
Numeric Operatorsl
Click here
Click here
Predicate Operators
Click here
Click here
Column Functions
Click here
sJ=:'g
]L>7a
TrySubjectReviewer$Conflict$C1$
Reviewer Response
L1S2S3S&
TrySubjectReviewer
Using Notes Internal Fields
N\New features
New Features in this Release
What is ODBC?
D\Data source"
Adding a Lotus Notes Data Source
D\Documentation, about
How to Use This Guide
U\Uninstalling NotesSQL
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
V\View column definitions
View Column Definitions
Software Requirements
E\Error messages
Error Messages
What is NotesSQL?
Installing NotesSQLELECT
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
When you add or modify a Lotus Notes data source, you can control a number of parameters by setting options in NotesSQL dialog boxes -- either the Lotus Notes ODBC 2.0 Setup dialog box (for user or system data sources) or the NotesSQL Connect dialog box (for file data sources).
This topic lists and explains the options in these NotesSQL dialog boxes. There are many options, but most have reasonable defaults. We suggest you try using the defaults first. The three dialog box fields that must be filled in are marked with asterisks.
Fields that ask for numeric values check your input. If you specify a value above the allowed maximum, the maximum value is substituted. If you specify a value below the allowed minimum, the minimum value is substituted. If you specify an invalid value, such as a letter, the default value is substituted.
You can also configure a data source by calling the ODBC API function SQLDriverConnect in an API program, passing keyword/value pairs as arguments. The descriptions below include the keywords that match each option.
368837628829232348
Data Source Name *
(Keyword: DSN)
Enter a name that identifies the data source in the Data Source Name field. For example, add the name "Employee" to identify the ODBC connection to an employee database.
368837628829232349
Description
Enter a description of the data source in the Description field. For example, add the description "Hire date, salary history, and current review of all employees" to describe the contents of the employee database.
368837628829232350
Server *
(Keyword: Server)
Enter the name of the Notes server that contains the Notes database you want to open in the Server field. Leave the field blank if the Notes database is on the local machine.
368837628829232351
Database *
(Keyword: Database)
Enter the path and name for the .NSF file you want to open in the Database field. For example,
C:\PERSONNEL\EMPLOYEE.NSF
for a file on a local disk, or
PERSONNEL\EMPLOYEE.NSF
for a database on a Lotus Notes server.
Whether the data is local or on a server, the path is relative to the Notes data directory on that machine. If the Notes data directory is D:\NOTES\DATA\, PERSONNEL\EMPLOYEE.NSF points to D:\NOTES\DATA\PERSONNEL\EMPLOYEE.NSF.
368837628829232352
Max Length of Text Fields
(Keyword: MaxVarcharLen)
This parameter specifies the maximum number of bytes NotesSQL allows in a string. This limits the number of characters returned from a Notes text field and the length of a string that can be inserted into a Notes text field.
The maximum value allowed is 15,360. The minimum value allowed is 2.
The default value is 254. If the database will be used exclusively or primarily with Lotus 1-2-3
, we recommend a value of 511. This is the maximum length for a string in a cell in Lotus 1-2-3.
368837628829232353
Max Number of Tables
(Keyword: MaxRels)
This parameter specifies the maximum number of tables in a single query.
The maximum value allowed is 100. The minimum value allowed is 1.
The default value is 20.
368837628829232354
Max Number of Subqueries
(Keyword: MaxSubquery)
This parameter specifies the maximum number of nested subqueries in a SQL statement.
The maximum value allowed is 100. The minimum value allowed is 1.
The default value is 20.
368837628829232355
Keep Temporary Indexes until SQLDisconnect
(Keyword: KeepTempIdx)
This option controls the saving of temporary indexes. Select this option to save temporary indexes until SQLDisconnect. Leave the option deselected to delete indexes at the end of each SELECT result.
This option is deselected by default.
368837628829232356
Return Notes Implicit Fields
(Keyword: ShowImplicitFlds)
This option controls whether certain Notes fields are provided through SQLStatistics and SQLSpecialColumns. Deselect it for maximum interoperability. Select it for backward compatibility with NotesSQL 2.0 or earlier.
This option is deselected by default.
368837628829232357
Map Special Characters
(Keyword: MapSpecialChars)
This option controls whether certain characters are mapped to the underscore character ( _ ). For details, see "
Click here
Mapping Notes Names to SQL Names
Click here
This option is selected by default.
368837628829232358
Max Length of Rich Text Fields
(Keyword: MaxLongVarcharLen)
This parameter is similar to Max Length of Text Fields, but applies to rich text fields.
The maximum value allowed is 15,360. The minimum value allowed is 2.
The default value is 512. If the database will be used exclusively or primarily with Lotus 1-2-3, we recommend a value of 511. This is the maximum length for a string in a cell in Lotus 1-2-3.
Notes databases have long text fields called rich text fields. The Body field of a Notes document is often a rich text field. If you want to retrieve all the data in such a field, be sure that the maximum string length you specify is large enough.
368837628829232359
SQL Statement Length
(Keyword: MaxStmtLen)
This parameter specifies the maximum length of a SQL statement passed to SQLPrepare or SQLExecDirect.
The maximum value allowed is 32,767 (32K). The minimum value allowed is 2.
The default value is 4,096 (4K).
368837628829232360
Thread Timeout (available on 32-bit Windows platforms only)
(Keyword: ThreadTimeout)
All calls are serialized so that they proceed one after the other. This parameter specifies the number of seconds one thread should wait for another thread to finish.
The maximum value allowed is 99,999. The minimum value allowed is zero (0). A value of zero means wait forever.
The default value is 60.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The CREATE TABLE statement defines a new table in the current database.
115414224029232561
Syntax
CREATE TABLE
<base table name>
{ <column name> <datatype>
DEFAULT
<default value> ] }
[ , ... ]
115414224029232562
Example
CREATE TABLE STAFF
(STAFF_NO CHAR(6),
LASTNAME CHAR(15),
FIRSTNAME CHAR(10),
HIREDATE DATE,
LOCATION CHAR(15),
SUPERVISOR CHAR(6),
SALARY FLOAT,
COMMISSION FLOAT)
115414224029232563
Arguments
<base table name> is the name of the table to create.
<column name> is the name of a column.
<datatype> is one of the following:
CHAR(n) or CHARACTER(n) where 1 <= n <= 254
DECIMAL(p, s) where p (precision) is 1 <= p <= 15 and s (scale) is 0 <= s <= p
NUMERIC(p, s) (same as DECIMAL)
SMALLINT
INTEGER
FLOAT
DOUBLE PRECISION
VARCHAR(n) or CHARACTER VARYING(n) (same as CHAR)
TIMESTAMP
The field sizes specified for CHAR, DECIMAL, NUMERIC, and VARCHAR fields are optional. If you specify a size for one of these fields, Notes ignores it when creating the field.
115414224029232564
Restrictions
NULL default value is not supported.
Column and table constraint definitions are not supported.
VARCHAR will produce a fixed length CHAR field, instead of a large text field.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Click here
Data Types
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
368837628829232348
To delete a Lotus Notes data source:
1.
Start the ODBC Data Source Administrator.
2. Select the type of data source you want to delete. On 32-bit Windows platforms, you can delete user, system, or file data sources.
3. Select the data source in the Data Sources list box.
4. Click Remove.
5. Click Yes to confirm the deletion.
If you are using 16-bit Windows, you cannot delete a file data source. Also note that the ODBC Data Source Administrator will present a different dialog box; however, the basic method is similar to the method described above.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
49641734429238162
To uninstall NotesSQL on 32-bit Windows platforms:
Click the uninstall shortcut in the Lotus Applications\NotesSQL Start menu folder, or:
1. Click the Windows Start button and choose Settings - Control Panel - Add/Remove Programs.
2. Select the NotesSQL driver from the list of programs and click Add/Remove.
3. When prompted to confirm deletion, click Yes.
4. Follow any further prompts that appear.
When you install NotesSQL, the installation program does not delete earlier versions of the NotesSQL driver DLLs. Instead, it renames them to "*.20x" (where x is the specific version digit; for example, "nsql32.204"). Therefore, it is possible to revert to an earlier version of NotesSQL after uninstalling version 2.05. For more information about this process, see the readme.txt file in your installation directory.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
These are tips for using NotesSQL with other applications:
Access supports table names of 64 characters. The ODBC specification supports 128. We've found that Access doesn't display the list of tables when a table has a name longer than 63 characters. This prevents connecting to a whole database even if the table you want has a smaller name.
Access doesn't support indexes on text fields longer than 254. For a DSN with 254 (the default) as the text field length, Access can use views with sorted text fields as indexes. If you change the setting in the configuration dialog to 255, Access will not be able to use the text field as a key in an index. It will display the following error message:
"Invalid field "..." in index or relation"
Also, updates of text fields will not work with the text field length set higher than 254 characters.
This issue does not apply to rich text fields.
In Crystal Reports(TM), after choosing a table, you are prompted to "Please select an alias name to refer to this file." The alias name entered should start with an alphabetic character. The default alias name is the same as the table name. If this starts with a non-alphabetic character, Crystal Reports gives an "Invalid Character" error.
In Visual Basic
and other applications, for a table to be updatable there must be a unique index on it. For details, see "
Click here
Using Notes Views as Indexes
Click here
Because of NotesSQL character mapping, SQL aliases that are the same as the view or form name are not allowed. For example, "SELECT * FROM MainqViewqAll MainqViewqAll" will not be accepted. It will return the error message "Alias name needed". You probably wouldn't type this SQL, since there is no point in having an alias that is the same as the original name. However certain applications, notably Crystal Reports, generate SQL like this. The workaround for this is to modify the alias name.
73957251229232354
These are known limitations or problems with NotesSQL:
A text field length of more than 254 is not recommended. It is available so that you can read long Notes text fields. Notes text fields are mapped to the ODBC data type VARCHAR. The ODBC specification for the maximum string length of a VARCHAR field is 254. Some applications won't work with longer fields. You may experience problems even if the data is shorter than 254 characters.
Scalar functions are supported only if there is underlying Notes support. Consequently, these scalar functions are not supported:
String: ASCII, INSERT, LOCATE
Numeric: TRUNCATE
The results in "
Click here
Driver Capabilities: SQLGetInfo Return Values
Click here
" were generated with ODBC 2.0.
There may be problems using categorized (collapsible) views. They don't map to the relational data model.
NotesSQL does not support hidden forms and views.
You can't use a form or view with a double quote ( " ) in the name.
View selection formulas marked as "Easy" (as opposed to "Formula") in the radio button on the view design may be hiding some detail. Look in the design synopsis to see the full view selection formula.
NotesSQL can update Authors and Readers fields in existing documents. During the update the item flags are maintained. When you insert a new document, the item flags are inherited from the field flags of the form used to create the document.
NotesSQL does not support multithreading on 16-bit Windows because of limitations in the underlying thread support.
Crystal Reports users may experience a problem with operations that involve INNER JOINs. We are currently working with Seagate to resolve this issue. Meanwhile, if you have problems using this construct, you may be able to work around them by editing your Microsoft Windows registry. (If you are unfamiliar with the registry, ask your system administrator to edit it for you.)
In this directory, you will see entries like the following on the right hand side of the window. These items represent various SQL syntax types for the different ODBC drivers installed on a machine.
Default value not setA
AccessDriver "odbcjt16,odbcjt32,qbodbc"
Informix "qeinf506,qeinf507"_
OJSyntax "iscdrv323"
OPenIngres "oplodbc,oplodb32"
PlusEqual "crora7,cror707,cror709"
PlusJoinDBMS "oracle"
SimbaDriver "Simba"
SpecialDlls "simclt32"
Sql2JoinDBMS ""
Sql2outerJoin "wsqlodbc,Simba"
StarEqual " sqlsrv32,sqlsrv,qess03"
StarJoinDBMS ""
If the key "Sql2outerJoin" is present, add ",nsql32" to the value (note the comma separator). In the above example, the result would look like:C
Sql2outerJoin "wsqlodbc,Simba,nsql32"
This should resolve the problem.
MainComment
Reviewer"
0S0E
$20Subject$Conflict$REF$C1$
TopicType
GuideMe
L1S4S5S
Subject
Using Notes Internal Fields
New Features in this Release
What is ODBC?"
Adding a Lotus Notes Data Source
How to Use This Guide
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
View Column Definitions
Software Requirements
Error Messages
What is NotesSQL?
Installing NotesSQL
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
The Universal Relation
Rich Text Fields
CREATE INDEX
CREATE TABLE
CREATE VIEW
Using the Universal Relation
DELETE Positioned
DELETE Searched
Using Notes Views as Indexes
DROP VIEW
INSERT
SELECT
Numeric Operators
HAVING
ORDER BY
UNION
WHERE
Predicate Operators
Data Types
ODBC API Functions
ALTER TABLE
DROP INDEX
DROP TABLE
UPDATE Searched
GROUP BY
Column Functions
FOR UPDATE
FROM"
Frequently Asked Questions (FAQ)
UPDATE Positionedrks only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Search
0S0E
Web Button
Use this button only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Categori_zea
_Edit Document
Send Docu_ment
_Forward
_Move To Folder...
_Remove From Folder
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
PURSAFO
$Author
$TITLE
$Index
$Formula
$FormulaClass
$ViewFormat
$Comment
$ACTIONS
$Author
$TITLE
$Index
$Formula
$FormulaClass
$ViewFormat
$Comment
$ACTIONS
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The following table describes how SQL components map to Notes components:
SQL Component
Notes Component
Comments
Table
Universal Relation
SQL Tables map to either Notes forms or Notes views.
However, a Notes database contains only one real table, referred to as the Universal Relation. This table has the same name as the database.
Column
Form Field
View Column
When creating a NotesSQL Table or View, it is best to avoid the use of column names that are ODBC or SQL reserved words or that contain characters other than letters, numbers, or underscores.
Index
A SQL Index maps to a Notes view in which all sorted columns refer directly to fields in a single form, and which selects documents from only that form.
A SQL View maps to a Notes view that selects documents from one form, in which all columns are calculable from the form.
When you create a view using SQL, a view is created in Notes that selects from one form. Except for private views, all Notes views are reported as SQL views.
When a Notes database has both a form and a view with the same name, NotesSQL cannot distinguish between them in SQL statements and the view cannot be accessed.
See related topics
Click here
Using SQL Tables Derived from Notes Forms and Views
Click here
Click here
The Universal Relation
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
Notes is more flexible about names than SQL. When naming a form or view, Notes allows many special characters and sequences of characters that are not part of the standard SQL syntax.
It is not uncommon to number views so they will appear in a certain order in the Create menu:
1. Products
2. Suppliers
If developers want hierarchical view names, they use the backslash:
Products\By Name
Products\By Age
These common Notes naming conventions are not allowed by standard SQL syntax, which does not allow the use of periods, spaces, and forward slashes in unquoted identifier names.
73957251229232354
Mapping
Because of this difference in syntax support, NotesSQL optionally maps certain characters to the underscore character ( _ ). This mapping is configurable for each Data Source. The default is to map the names. We recommend using the default unless it causes a form or view to be inaccessible. This can happen when two similar names (for example, "Employees$" and "Employees_") are mapped to the same name.
If you choose to map names, NotesSQL maps forms, views and their aliases, form fields, and view columns. The characters mapped are:
~ ` ! @ # $ % ^ & *
( ) - + = { } [ ] \
: ; " ' < > , . / ?
and the space character.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL recognizes Notes forms and views as tables. In addition to forms and views, every Notes database contains a table that has the same name as the database. This table is called the Universal Relation. The Universal Relation contains all fields defined in all forms in the Notes database. The Universal Relation is the only true table in a Notes database. As a result, SQL tables created by NotesSQL behave more like SQL views than like traditional relational database tables.
For example, with NotesSQL you can create a Notes form with the CREATE TABLE statement. However, the DROP TABLE statement deletes the Notes form but does not delete any data from the database. Using DROP TABLE with NotesSQL is like deleting a SQL view, in that the data remains in the database. You can view the data through other views that use the same field names, or by referencing the Universal Relation table.
Continuing this example, if you create a new table with the same name as a previously deleted table, and use some of the field names from the deleted table, you could find data in the table before you insert any data. This is because the table is actually a view of existing data in the Universal Relation table. Documents (records) in the Notes database contain the name of the form used to create them. NotesSQL uses the form name stored in the document to identify the document when selecting from a form.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
You must have read/write access to a database to use its Universal Relation. Without read/write access, you cannot perform any operation (including SELECTs) on the Universal Relation.
The same field name can be used in more than one form with different data types in a Notes database. Therefore, you must use explicit field names in a SELECT clause that references the Universal Relation. In particular, you cannot use SELECT *. You can only perform text operations on fields in the Universal Relation because the data type for all fields is character.
The Universal Relation can participate in a self-join but cannot participate in any other kind of join.
NotesSQL supports the SELECT statement on the Universal Relation. NotesSQL does not support this on a read-only .NSF file.
NotesSQL supports the CREATE VIEW statement on the Universal Relation.
NotesSQL does not support INSERT, DELETE, UPDATE, DROP TABLE, or DROP VIEW statements on the Universal Relation.
NotesSQL uses the type "UNIVERSAL" to differentiate the Universal Relation in the result set of SQLTables.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The CREATE VIEW statement defines a new view in the current database.
115304224029232561
Syntax
CREATE VIEW
<view name>
<column list>
<query specification>
115304224029232562
Example
CREATE VIEW NYCUST (COMPANY,ADDRESS) AS SELECT COMPANY,ADDRESS FROM CUSTOMER WHERE STATE = 'NY'
115304224029232563
Arguments
<view name> is the name of a new view.
<column list> is a comma-delimited list of column names.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The Notes Personal Address Book (NAMES.NSF) is a good database to use as an example to compare the use of forms or views in a database. The Personal Address Book database includes
A form called Person
A view called People with a sort key on LastName
The following statement is the most efficient way to find people in the Personal Address Book sorted by LastName:
SELECT LastName
FROM People
ORDER BY LastName
People is a Notes view. This query is efficient because NotesSQL can use the index already associated with the People view that lists LastName in the right order. Now assume you want to list people sorted by their mailing addresses. You could use the following statement:
SELECT LastName, Mail_Address
FROM People
ORDER BY Mail_Address
Since the People view is not sorted on Mail_Address, NotesSQL uses the People index, generates a temporary table, and creates a temporary index on Mail_Address. This results in slower performance. a
A more efficient way to achieve the same result is to issue the following statement:
SELECT LastName, Mail_Addressa
FROM Persone
ORDER BY Mail_Addressc
Person is a Notes form. If there is no index on Mail_Address, NotesSQL generates a temporary index on Mail_Address but does not need to generate a temporary table. This statement is faster than the previous statement, which used ORDER BY on a view-based table. This statement can be executed even faster if the user creates an index in Notes or by using the CREATE INDEX statement in NotesSQL.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL will use an existing Notes view as an index if it meets the following criteria:
The view selection formula in the Notes view design is either SELECT Form = "name" or SELECT @All.
SELECT Form = "name" is faster than SELECT @All, unless all documents in the database were created using the same form. Note that views created with SELECT @All are not described as indexes through SQLStatistics. They are used by NotesSQL to improve performance.
At least one column in the view is sorted with case-sensitive sorting enabled.
Each sorted column in the view is defined as a simple reference to a field in that form (no formulas).
NotesSQL uses such views as indexes if they are available; however, their absence does not mean that your SQL queries will fail. It simply means that the query optimizer must generate a temporary index to support the query. For more information, see "
Click here
Using SQL Tables Derived from Notes Forms and Views
Click here
An easy way to see view design information at a glance in Notes is to select File - Database - Design Synopsis. In the dialog box, choose Views, then click OK. Notes generates a detailed synopsis document that includes all the above information and more.
73957251229232354
Unique Indexes
In Notes there is a view property labeled "Unique keys in index (for ODBC access)." When this setting is checked, NotesSQL describes the sorted columns of the view as the keys of a unique index. This happens through the ODBC call SQLStatistics. Having these indexes allows several third-party applications to issue updates against NotesSQL.
Use some care with this feature. Checking the box doesn't make the view a unique index in the relational database sense. Specifically, it doesn't prevent duplicate records. It only puts the first document with a particular sorted value into the view. Therefore, if you are using this feature, updates may affect more than one record and ORDER BYs may generate small result sets.
Here are some guidelines to follow to avoid these problems:
Only use the checkbox for one view that selects from each form that will be updated.
Choose a view with sorted columns that are each one field (not a formula or expression).
Choose a view with sorted columns that together uniquely identify a record.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL provides access to several Notes internal fields. These fields are described through the functions SQLColumns and SQLSpecialColumns when the "Return Notes Implicit Fields" configuration option is selected. However, even when the fields are not described through these functions, they are always available for use in SQL statements. The fields are updated automatically and are therefore read-only.
Treat the names of these fields as reserved; do not use them to name your own fields.
The fields are:T
Field
Description
NoteID
The Note ID of the document. This number uniquely identifies a document in one copy of a database. (Note IDs for a document may vary among replicas of the same database.) To get the Note ID in the format that a Notes C API program would use, bind it as SQL_C_LONG.
SQL_DOUBLE
NoteRef
For response documents, NoteRef is the Note ID of the parent document. For non-response documents, NoteRef is 0. To get the NoteRef in the format that a Notes C API program would use, bind it as SQL_C_LONG.
SQL_DOUBLE
NoteCreated
The TIMESTAMP of the creation of the document.
SQL_TIMESTAMP
NoteModified
The TIMESTAMP of the last modification to the document.
SQL_TIMESTAMP
NoteFile
The number of file attachments in this document.
SQL_FLOAT
NoteForm
The embedded form of the document. This is typically the name of the form used to create the document.
SQL_VARCHAR
NoteFormType
The type of the form used to create the document. For the documents NotesSQL provides, this is typically "Document".
SQL_VARCHAR
NoteUNID
The Universal Note ID (UNID) of the document, a 32-byte string. This value uniquely identifies a document across all replicas of a database.
NoteRefUNID
The $Ref of the document in UNID format (a 32-byte string). This value also uniquely identifies a document across all replicas of a database.b
For example with a discussion database, you could use the following SQL statement to see that all responses were created after their corresponding Main Topics:
Internal fields are not subject to optimization by the query optimizer, with the exception of NoteUNID and NoteRefUNID. Note that not all SQL statements can be optimized, whether or not they contain internal field references. The example above will be optimized; we recommend that you use NoteUNID and NoteRefUNID instead of NoteID and NoteRef whenever you join two tables.d
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
Error messages come from three areas: the ODBC Driver Manager, NotesSQL, or Notes.
The Driver Manager detects incorrect sequences of ODBC API calls and invalid parameter values.
NotesSQL detects errors when parsing and validating SQL commands.
Notes detects errors when executing database operations.
Error messages have the following format:
[vendor][ODBC-component][data source]message-text
The prefixes in square brackets ([ ]) identify the location of the error.
The following table shows the format of error messages returned by the ODBC Driver Manager, NotesSQL, and Notes:
Error message
Error location
32-bit Windows:
[Microsoft][ODBC Driver Manager]
message-text
16-bit Windows:
[Microsoft][ODBC.DLL]
message-text
OS/2:t
[Visigenic][ODBC Driver Manager]
message-text
ODBC Driver Manager
[Lotus][ODBC Lotus Notes]
message-text
NotesSQL
[Lotus][ODBC Lotus Notes][Lotus Notes Server]Notes API error:
message-text
Lotus Notes or Domino
73957251229232354
Errors reported by NotesSQL
The driver returns an error message and the appropriate SQLSTATE when an error is detected in NotesSQL. The name of a data source is not included in the error message.
73957251229232355s
Errors reported by Notes
When an error is reported by Notes, NotesSQL returns SQLSTATE S1000 and the error message returned by Notes.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
If a column in a view does not refer directly to a field, NotesSQL creates a name for that column in the result set. The name is the "$" character followed by a number (for example, "$2").
You can change the column name that NotesSQL sees by editing the design properties of the column. When the Column Properties dialog is visible, click on the Advanced tab. Change the "Programmatic name" to something more meaningful.
If a view column definition or a Computed for Display field in a form contains one of the @functions listed below, NotesSQL returns no results for that column. These columns are not reported by SQLColumns. These columns display data when viewed from Notes, but this data is not available through a SQL query.
@DBTitle
@DeleteDocument
@DeleteFields
@DocChildren
@DocDescendants
@DocLevel
@DocNumber
@DocParentNumber
@DocSiblings
@Error
@IsCategory
@IsExpandable
@Unavailable
Expressions passed in SQL statements must be either valid SQL expressions or valid Notes expressions. Otherwise, the results are unpredictable.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
73957251229232354v
ODBC SQL to Notes data type mapping:
ODBC SQL Data Type
Lotus Notes Data Type
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_DECIMAL
Number, Fixed format
SQL_NUMERIC
Number, Fixed format
SQL_SMALLINT
Number, Fixed format
SQL_INTEGER
Number, General format
SQL_REAL
Number, General format
SQL_FLOAT
Number, General format
SQL_DOUBLE
Number, General format
SQL_DATE
SQL_TIME
SQL_TIMESTAMP
In addition to the SQL data types, Notes supports two additional data types:
Click here
Multi-Valued Fields (List Fields)
Click here
Click here
Rich Text Fields
Click here
SQLGetTypeInfo returns ODBC SQL data types. All conversions in Appendix D of the Microsoft ODBC SDK Programmer's Reference are supported for the ODBC SQL data types listed above.
SQL_TIMESTAMP fields do not support fractions of a second.
SQL_VARCHAR or SQL_LONGVARCHAR data types cannot be used to create rich text fields. They create text fields.
73957251229232355
Notes to SQL data type mapping:
Lotus Notes data type
ODBC SQL data type
Number, Fixed format
Number, Percent format
SQL_DECIMAL
Number, General format
Number, Scientific format
SQL_FLOAT
Depending on format, this can be SQL_TIME, SQL_DATE, or SQL_TIMESTAMP
SQL_VARCHAR
Keyword
SQL_VARCHAR
Multi-value list
SQL_VARCHAR
Rich text field
Text portion only, as SQL_LONGVARCHAR
Section
Not supported
Numeric values greater than 10 to the 99th power are not supported. Some products that use ODBC drivers have limitations on the exponent and precision size of real numbers. Please check the limits of the product you are using for additional limit information.
NotesSQL does not check the keyword list for Keyword fields. It is possible to insert values not on the keyword list when the "allow values not in list" box is not checked.
NotesSQL does not support Notes Section security. Users of NotesSQL can read and modify fields anywhere on a form.
You cannot insert a string longer than 15,360 bytes into a rich text field.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The positioned DELETE statement removes the last row fetched (the current row).
115364224029232561
Syntax
DELETE FROM
<table name>
WHERE CURRENT OF
<cursor name>
115364224029232562
Example
DELETE FROM INVENTORY WHERE CURRENT OF SQL_CUR_0
115364224029232563
Arguments
<table name> is the name of the table where you want to remove data.
<cursor name> is the name of the cursor pointing to the row to be removed.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Subject$4$Conflict$REF$C1$
Subject
Using Notes Internal Fields
New Features in this Release
What is ODBC?"
Adding a Lotus Notes Data Source
How to Use This Guide
Uninstalling NotesSQL"
Mapping Notes Names to SQL Names
View Column Definitions
Software Requirements
Error Messages
What is NotesSQL?
Installing NotesSQL
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
The Universal Relation
Rich Text Fields
CREATE INDEX
CREATE TABLE
CREATE VIEW
Using the Universal Relation
DELETE Positioned
DELETE Searched
Using Notes Views as Indexes
DROP VIEW
INSERT
SELECT
Numeric Operators
HAVING
ORDER BY
UNION
WHERE
Predicate Operators
Data Types
ODBC API Functions
ALTER TABLE
DROP INDEX
DROP TABLE
UPDATE Searched
GROUP BY
Column Functions
FOR UPDATE
FROM"
Frequently Asked Questions (FAQ)
UPDATE Positioned
Show Search Bar
0S0E
Hide Search Bar
0S0E
Domino
ViewNextPage
Web Button
This button works only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Previous
Domino
ViewPreviousPage
Web Button
This button works only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Search
0S0E
Web Button
Use this button only if you're browsing this information over the Web.
0R6S
$$WebClient
0S0E
Categori_zea
_Edit Document
Send Docu_ment
_Forward
_Move To Folder...
_Remove From Folder
O=Lotus Notes
O=Lotus Notes
PURSAFO
|.:#U
O=Lotus Notes
CN=Lotus Notes Template Development/O=Lotus Notes
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
74017251229232361e
Which versions of Notes can NotesSQL 2.05 access?
Notes 4.0 and above.
205816464029255036
Which Notes client software must I install to use NotesSQL 2.05 access?
For client-based third-party applications, NotesSQL and the Designer or Desktop software must be installed on your local machine. See readme. txt for detail on specific versions supported.
257363766429254867
Can I use NotesSQL 2.05 with a File DSN?
Yes. NotesSQL 2.05 is the first version to fully support File Data Sources (File DSNs). File DSNs are stored in a file. When a File DSN is used, the Driver Manager makes the connection to the source using the information in the .dsn file. This file can be manipulated like any other file. A File DSN does not have a data source name, as a machine data source does, and is not registered to any one user or machine.
257363766429254868
How can I improve the performance of my NotesSQL application?m
If you use a GROUP BY, JOIN, or WHERE clause, you may want to create a view to act as an index for the field in the clause. Otherwise, the query optimizer generates a temporary index to enhance the performance of the SQL statement. For more information about using views as indexes, see "
Click here
Using Notes Views as Indexes
Click here
257363766429254869
Can I use a Universal Naming Convention (UNC) file name to connect to a Notes database?
Yes. You can use a UNC file name if the database is on a local server (a server accessible through your network file system, not a Domino server). UNC file names for Notes databases have the form "\\<ServerName>\<ShareName>\<Directory1>\<Directory2>\<...>\<DatabaseName>.NSF".
257363766429254871
How can a third party application access NotesSQL 2.05 internal fields?
NotesSQL internal fields are described through the function SQLColumns when the "Return Notes Implicit Fields" configuration option is selected. However, even when the fields are not described through this function, they are always available for use in SQL statements.
257363766429254870
Can I access the UNID (Universal Note ID) and the $REF fields of a Notes document? g
Yes. Two new internal fields introduced in NotesSQL 2.05, NoteUNID and NoteRefUNID, provide this access. t
257363766429254872
Can I use internal fields in a join?
Yes. However, internal fields are not subject to optimization by the query optimizer, with the exception of NoteUNID and NoteRefUNID.
257363766429254873
What changes were made in NotesSQL 2.05 to improve the NotesSQL ODBC Setup?
There is a new interface that lets you browse all available servers and databases.
16971878429254897
Which platforms does NotesSQL 2.05 support?
NotesSQL 2.05 is updated for Win32 and Win16 platforms. Mac and OS/2 users may continue to use NotesSQL 2.04.@
16971878429254898
Can I use NotesSQL 2.05 on a Domino server without installing a Notes client?
73957251229232354
Can I share ODBC handles between threads?
Yes, on Win32 platforms. The restriction that an application can't share ODBC handles between threads was removed in NotesSQL 2.04. This enhancement removed problems we've seen in using NotesSQL with Microsoft Internet Information Server (IIS) and the Internet Database Connector (IDC) component, Impromptu from Cognos, and SequeLink from Intersolv.
73957251229232355
Can I use NotesSQL with the JDBC-ODBC bridge?
Yes. However, if you want to create a Java applet that uses the JDBC-ODBC bridge and NotesSQL to access Notes data, remember that the bridge is not 100% Pure Java, and is therefore by definition not secure. NotesSQL itself relies on Notes to ensure the security of the Notes data you access. It is the responsibility of the applet creator to make the program a trusted applet in the context of the browser in which it runs.
73957251229232356
What Java tools can I use to connect to NotesSQL using the JDBC-ODBC bridge?
NetObjects BeanBuilder(TM), which includes a part on the palette that lets you access ODBC data sources, and Visual Age for Java e-business using the Data Access Builder tool. You'll need NotesSQL 2.05 and the most recent version of each product.
205816464029255037
How can I use NotesSQL with eSuite?
eSuite uses the InfoBus for data access. To provide interoperability with JDBC data sources, the eSuite DevPack provides a converter applet called JdbcSource. JdbcSource will pass data to the InfoBus from JDBC. With JdbcSource, one can connect to any JDBC source. To use eSuite with NotesSQL, you also need another piece of software to translate between ODBC (NotesSQL) and JDBC. The JDBC-ODBC bridge or some middleware such as Intersolv SequeLink or Symantec dbAnywhere will do this translation.
73957251229232357
Can I use NotesSQL with NetObjects Fusion?
Yes. NetObjects Fusion
,s data publishing capabilities allow you to import data from any ODBC driver. An example is provided in the Fusion4 sample.
73957251229232358
Can I access NotesSQL using Active Server Pages?
Yes. You need NotesSQL 2.05 and IIS 4.0 using ActiveX Data Objects (ADO). An example is provided in the Vb_ADO sample.
73957251229232359u
Can I use FrontPage 98 to generate Active Server Pages (ASP) and access NotesSQL 2.05?
Yes. However, to run it you need an IIS server or a Personal Web Server.
73957251229232360
Where can I get NotesSQL 2.05?
You can get the latest version of NotesSQL from the Lotus Web site (http://www.lotus.com). On the home page, click Products in the left-hand frame. Then scroll down in the frame and click NotesSQL. From the NotesSQL page, download and run the NotesSQL self-extracting executable for your platform.Q
74017251229232362@
Can I produce a report that joins Notes data and data from my relational database?
This depends on your reporting tool. If it supports heterogeneous joins, sure.
74017251229232363
Can I update Notes databases via NotesSQL 2.05?
Yes. (Of course, we have not tested every possible update scenario, so please let us know if you experience any problems.)
74017251229232364
What security is in place when I use NotesSQL 2.05 to access Notes?
The same security that is in place with any Notes C API application. This is why we don't support sending a user name and password through the API; doing so would breach Notes security. To run an application unattended without ever receiving a password prompt, you must use a non-password-protected ID.
You can remove password protection from your ID by clearing it (File - Tools - User ID - Clear Password), unless your Domino administrator required a password to be used when your ID was created. In this case, you won't be able to clear it.
423141404829251039
The NotesSQL installation process seems different. What has changed?
On 32-bit Windows platforms, the NotesSQL installation program now updates existing machine DSNs to use the new driver. (It also makes it possible for you to revert to an earlier version of NotesSQL, if necessary.) For more information about this process, see the readme.txt file in your installation directory or
Click here
"Installing NotesSQL
Click here
423141404829251038
How can I contact the NotesSQL team regarding bugs, questions, or feedback?
To communicate with the NotesSQL team, post your question, bug report, or feedback in the NotesSQL section of the Developer Central discussion forum, at http://www.lotus.com/rw/devchat.nsf/by+Category.
Note that the NoteSQL team does not provide technical support. If you require support, please contact Lotus Support. In the US, call 800-437-6391. Outside the US, see http://www.support.lotus.com/csserv.nsf/Links/21ae for the number of the nearest Support Center.
You may also be able to find answers to questions in the following user forums:
Business Partner Forum: http://partners.lotus.com/zone1.nsf
The following table lists the C language #defines for the fInfoType argument and the corresponding values returned by SQLGetInfo.
SQLGetInfo values returned by the ODBC Driver Manager are not listed here.
In this table, "No" means that the driver returns "N", "Yes" means that the driver returns "Y", and "None" means that the driver returns the null string.
fInfoType Value (#define)e
Lotus NotesSQL Return Values
SQL_ACCESSIBLE_PROCEDURES
SQL_ACCESSIBLE_TABLESe
SQL_ACTIVE_CONNECTIONS
0 = No Limit
SQL_ACTIVE_STATEMENTS
0 = No Limit
SQL_ALTER_TABLE
SQL_AT_ADD_COLUMN
SQL_BOOKMARK_PERSISTENCE
SQL_BP_DELETE
SQL_BP_SCROLLd
SQL_BP_UPDATEo
SQL_COLUMN_ALIAS
SQL_CONCAT_NULL_BEHAVIOR
SQL_CB_NON_NULL
SQL_CONVERT_BIGINT
SQL_CONVERT_BINARY
SQL_CONVERT_BITo
SQL_CONVERT_CHAR
0x000381FF =
SQL_CVT_CHAR |
SQL_CVT_NUMERIC |
SQL_CVT_DECIMAL |
SQL_CVT_INTEGER |
SQL_CVT_SMALLINT | s
SQL_CVT_FLOAT |
SQL_CVT_REAL | u
SQL_CVT_DOUBLE | t
SQL_CVT_VARCHAR |
SQL_CVT_DATE | d
SQL_CVT_TIME | r
SQL_CVT_TIMESTAMPn
SQL_CONVERT_DATE
0x00028101 =
SQL_CVT_CHAR |
SQL_CVT_VARCHAR |
SQL_CVT_DATE |
SQL_CVT_TIMESTAMP
SQL_CONVERT_DECIMALe
SQL_CONVERT_DOUBLE
SQL_CONVERT_FLOATs
SQL_CONVERT_INTEGERi
SQL_CONVERT_NUMERICn
SQL_CONVERT_REAL
SQL_CONVERT_SMALLINT
0x000001FF =
SQL_CVT_CHAR |
SQL_CVT_NUMERIC |
SQL_CVT_DECIMAL |
SQL_CVT_INTEGER |
SQL_CVT_SMALLINT|
SQL_CVT_FLOAT |
SQL_CVT_REAL |
SQL_CVT_DOUBLE |
SQL_CVT_VARCHAR
SQL_CONVERT_FUNCTIONS
SQL_FN_CVT_CONVERT
SQL_CONVERT_LONGVARBINARY
SQL_CONVERT_LONGVARCHAR
SQL_CONVERT_TIME
0x00030101 =
SQL_CVT_CHAR |
SQL_CVT_VARCHAR |
SQL_CVT_TIME |
SQL_CVT_TIMESTAMP
SQL_CONVERT_TIMESTAMP
0x00038101 =
SQL_CVT_CHAR |
SQL_CVT_VARCHAR |
SQL_CVT_DATE |
SQL_CVT_TIME |
SQL_CVT_TIMESTAMP
SQL_CONVERT_TINYINT
SQL_CONVERT_VARBINARY
SQL_CONVERT_VARCHAR
0x000381FF =
SQL_CVT_CHAR |
SQL_CVT_NUMERIC |
SQL_CVT_DECIMAL |
SQL_CVT_INTEGER |
SQL_CVT_SMALLINT |
SQL_CVT_FLOAT |
SQL_CVT_REAL |
SQL_CVT_DOUBLE |
SQL_CVT_VARCHAR |
SQL_CVT_DATE |
SQL_CVT_TIME |
SQL_CVT_TIMESTAMP
SQL_CORRELATION_NAME
SQL_CN_ANY
SQL_CURSOR_COMMIT_BEHAVIOR
SQL_CB_PRESERVE
SQL_CURSOR_ROLLBACK_BEHAVIOR
SQL_CB_DELETE
SQL_DATA_SOURCE_NAME
(Data source name from connection.)
SQL_DATA_SOURCE_READ_ONLY
(If the database is local, this is the read-only setting of the database file. If the database is on a server, this indicates whether the user has write access.)
SQL_DATABASE_NAME
(Database name from connection.)
SQL_DBMS_NAME
"Lotus Notes"
SQL_DBMS_VER
"Lotus Notes Version X" (X = 4 or 5)
SQL_DEFAULT_TXN_ISOLATION
SQL_DRIVER_NAMEs
(Name of driver DLL, depends on platform.)
SQL_DRIVER_ODBC_VER
"02.00"
SQL_DRIVER_VER
"2.05.XXXX" (Returns the version of the installed driver.)
NotesSQL supports all ODBC API functions with the following exceptions:
SQLColumnPrivileges
SQLTablePrivileges
SQLForeignKeys
SQLProcedureColumns
SQLProcedures
These functions are not implemented in the driver because there is no support for them in Notes.
The following describes how particular functions are implemented in NotesSQL, where the implementation differs from the ODBC specification.
381924972829232708
SQLCancel
The SQLCancel function (without the SQLSetStmtOption ASYNC_ENABLE option) is useful for interrupting a SQLPutData function when the return of a long data field requires additional SQLPutData functions.
381924972829232709
SQLColumns
If a column in a view does not refer directly to a field, NotesSQL names the column in the result set. The name is the character "$" followed by a number (for example, "$2").
When you call SQLColumns, the optional REMARKS column is empty.
Note that SQLColumns filters out columns that have no relation. You cannot create or select a column if it is not part of a relation in SQL.
381924972829232710
SQLGetConnectOption
SQLSetConnectOption
These functions accept all valid options.
The options that affect the state of the driver are:
SQL_ACCESS_MODE
SQL_QUIET_MODE
For SQLSetConnectOption, SQL_AUTOCOMMIT returns SQL_SUCCESS if given the argument 1, and SQLGetConnectOption always returns 1.
The following options return SQLSTATE S1C00 (Driver not capable) if given valid input, or S1009 (Invalid argument) in case of invalid input:
SQL_CURRENT_QUALIFIER
SQL_LOGIN_TIMEOUT
SQL_PACKET_SIZE
SQL_TRANSLATE_DLL
SQL_TRANSLATE_OPTION
SQL_TXN_ISOLATION
381924972829232711
SQLGetStmtOption
SQLSetStmtOption
These functions accept all valid options. In some cases, SQLSetStmtOption does not accept all possible values:
SQL_BIND_TYPE
SQL_CONCURRENCY = SQL_CONCUR_LOCK or SQL_CONCUR_READ_ONLY
SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY or SQL_CURSOR_STATIC
SQL_MAX_LENGTH
SQL_MAX_ROWS
SQL_NOSCAN = SQL_NOSCAN_OFF
SQL_RETRIEVE_DATE
SQL_ROWSET_SIZE
The following option returns SQLSTATE 01S02 on SQLSetStmtOption and zero on SQLGetStmtOption:
SQL_QUERY_TIMEOUT
The following options return SQLSTATE S1C00:
SQL_ASYNC_ENABLE
SQL_KEYSET_SIZE
SQL_SIMULATE_CURSOR
381924972829232712
SQLSetParam
SQLSetScrollOptions
These functions are supported only for backward compatibility with ODBC 1.0 applications.
381924972829232713
SQLTablesU
The following table types may be returned:
TABLEi
SYNONYMa
UNIVERSALe
Every Notes database includes a table that has the same name as the database. This is referred to as the Universal Relation table. It contains all fields in all forms in the Notes database. Tables created with the Notes driver are more similar to SQL views than to traditional relational database tables.
381924972829232714
SQLTransact)
SQLTransact is supported in a limited way. Since Notes does not provide any transaction mechanism, SQL_COMMIT is supported, but SQL_ROLLBACK returns an error.
The searched DELETE statement removes specified rows from a table.
115364224029232561
Syntax
DELETE FROM
<table name> [
WHERE
<where clause> ]
115364224029232562
Example
DELETE FROM NYCUST WHERE COMPANY = 'Interior Designs'
115364224029232563
Arguments
<table name> is the name of the table where you want to remove data.
<where clause> specifies the rows to remove. If no WHERE clause is specified, all rows in the table are removed.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Click here
WHERE
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL supports most SQL statements and clauses in the ODBC Minimum and Core grammar. The following table describes the exceptions:
Grammar
Exception
There is no explicit NULL in Notes. NotesSQL uses NULL to provide access to the Notes function @IsAvailable. See "NULL" below for details.
ORDER BY clause
NotesSQL supports ordering by expressions that aren't in the project list. This is not standard SQL but many applications use it.
WHERE clause and TIMESTAMP fields
Timestamp fields need to be fully padded when used in WHERE clauses if the time component is not used. That is, if time is not used, the following format is required:
'1995-12-31 00:00:00'
ALTER TABLE
The following keywords are not supported:
NOT NULL
CREATE INDEX
The UNIQUE keyword is not supported
CREATE TABLE
The following keywords are not supported:m
NOT NULL
UNIQUE
PRIMARY KEYV
REFERENCES _
No table constraint definition
PARAMETER RESTRICTIONS
Parameters are supported in INSERT, DELETE, and SELECT SQL statements. They cannot be used with CREATE TABLE, CREATE VIEW, and other statements that manipulate the structure of tables and views (DDL). Arrays of parameters are not supported.
RESTRICT and CASCADE
Dependencies are only recognized if they were originally created using the driver. DROP TABLE CASCADE will only remove dependent views created using the driver. DROP TABLE RESTRICT will only prevent the removal of a table if a dependent view was created using the driver.
GRANT and REVOKE
Not supported. All access control is handled implicitly by Notes.
115304224029232561
NotesSQL uses NULL to provide access to the Notes function @IsAvailable. For example, suppose you are comparing a field to NULL, as in:
SELECT * FROM Employee WHERE Name IS NULL
NotesSQL checks @IsAvailable("Name"). If @IsAvailable("Name") is TRUE, the WHERE clause is FALSE. Otherwise, the WHERE clause is TRUE.
Continuing the parallel, updating or inserting NULLs through NotesSQL removes fields from documents. So for example, the following will create a document with no Name field:@
INSERT INTO Employee (EmpNo, Name) VALUES (10, NULL)
Likewise, the following will remove the Name field from a document:
UPDATE Employee SET Name=NULL WHERE EmpNo=10
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
115304224029232561
Conventions
The following table lists the conventions used to describe the syntax for SQL statements.
Convention
Description
<argument>
Information that the user or application must provide.
CREATE
Syntax that must be entered exactly as shown, including function names.
Optional items or, if in bold text, brackets that must be included in the syntax. Square brackets also delimit syntax elements.e
Separates mutually exclusive choices in a list of syntax elements.
Delimits a list of mutually exclusive choices in a syntax line.
Indicates that the preceding grammar element may be repeated several times.
[ , ... ]
Indicates that the preceding grammar element may be repeated in a comma-delimited list.
SALESo
Names of fields in Notes databases.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
115364224029232561
Naming Rules
A column, index, or view name can be up to 32 characters long. A table name can be up to 128 characters long.
Index, view, and table names can consist of letters, digits, underscores ( _ ), dollar signs ( $ ), and spaces. If an index, view, or table name contains a space or is the same as a SQL reserved word, it must be enclosed in double quotes.
Column names can consist of letters, digits, underscores ( _ ), and dollar signs ( $ ). Column names connot contain spaces or conflict with a SQL reserved word.
115364224029232562
Creating Columns, Indexes, Tables, and Views
When creating an index or view, remember that index names and view names must be unique within a database. Don't use the name of an existing index or view.
When creating a table, remember that table names must be unique within a database. Don't use the name of an existing table or view.t
When creating or altering a table, remember that column names must be unique within a table. Don't repeat the name of another column in the table.
115364224029232563
Name Visibilityt
If a Notes database contains both a view and a form with the same name, NotesSQL will see the form but not the view.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL supports multiple values in fields. NotesSQL contains information about which fields can have multiple values and the underlying data type for these values.
NotesSQL does not support multi-valued (list) fields in DDL statements (CREATE TABLE, ALTER TABLE, CREATE INDEX, CREATE VIEW).
Use the following notation in DML statements (SELECT, INSERT, UPDATE searched, UPDATE positioned, DELETE searched, DELETE positioned) to specify a list of values:
'string;string;string'
(See below for more information about the correct separator character to use.)
115364224029232561
Text Form Fields
NotesSQL returns all the data in multi-valued form fields composed of text as a single string. Data items are separated by the display separator character (the character defined in Notes as the one to use to separate multiple values when displaying them). For example:
'a;b;c'
An extra semicolon can appear in the result if the data was entered in Notes, any character other than a semicolon was used as the display separator character, and the list contains an item that includes a semicolon.
NotesSQL accepts a list of strings for insertion in a multi-valued form field of type Text. You must separate the strings with the display separator character defined for that field. For example, if a comma is the display separator character for a field, you must specify a string like:
'a,b,c'
This value creates a list in the Notes document if the field allows multiple values. If the field does not allow multiple values, the value appears in Notes as a single string. Data retrieved through NotesSQL produces the same result in either case.
115364224029232562
Numeric and Date Form Fields
NotesSQL returns only the first value in the list when the multi-valued fields are numeric or date fields.
Currently, NotesSQL accepts only a single numeric or date value for insertion in a multi-valued field of type Numeric or Date.
78295372829252458
Display separator characters in view columns
Notes lets you define a display separator for a view column. NotesSQL will return that display separator only if the column uses a formula to create its content. If not, it returns the display separator defined for the form field whose content is displayed in the column.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL supports the following numeric operators in expressions:
Operator
Meaning
Addition
Subtraction
Multiplication
Division
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL returns only the text parts of a Notes rich text field. NotesSQL cannot create a rich text field.
The driver supports only the LIKE operator in a WHERE clause for testing rich text fields. The driver does not support any other tests on tich text fields.
Additional restrictions on rich text fields:
If a rich text field in Notes has embedded bitmaps or large attachments, text following the bitmap may not be returned by NotesSQL.
If a rich text field contains only blank lines, NotesSQL will not return data from that field.
String constants in a LIKE statement are restricted to a maximum of 254 characters, including any embedded carriage returns.
You cannot insert a string longer than 15,360 bytes into a rich text field.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The ALTER TABLE statement adds one or more columns to a table.
<base table name> is the name of the table to be altered.
<column name> is the name of the column to be added.
<data type> is one of the following:
CHAR(n) or CHARACTER(n) where 1 <= n <= 254
DECIMAL(p, s) where p (precision) is 1 <= p <= 15 and s (scale) is 0 <= s <= p
NUMERIC(p, s) (same as DECIMAL)
SMALLINT
INTEGER
FLOAT
DOUBLE PRECISION
VARCHAR(n) or CHARACTER VARYING(n) (same as CHAR)
TIME
TIMESTAMP
The field sizes specified for CHAR, DECIMAL, NUMERIC, and VARCHAR fields are optional. If you specify a size for one of these fields, Notes ignores it when creating the field.
115304224029232564
Restrictions
NULL and NOT NULL are not supported.
ALTER TABLE DROP is not supported.
VARCHAR will produce a fixed length CHAR field, instead of a large text field.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Click here
Data Types
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The CREATE INDEX statement defines an index for a table.
300828243229232709
Syntax
CREATE INDEX
<index name>
<base table name>
<column name> [
] [ , ... ]
300828243229232710
Example
CREATE INDEX CUSTNDX ON CUSTOMER (LASTNAME ASC)
300828243229232711
Arguments
<index name> is the name of the index to create.
<base table name> is the name of the table to be indexed.
<column name> is the name of a column to include in the index. The entire list of column specifications is enclosed in parentheses and items are separated by commas. The column may not be of type VARCHAR.
ASC or DESC specifies the order of the index, either ascending (ASC) or descending (DESC). If not specified, the index is built in ascending order.
This operation creates a new view in the Notes database.
300828243229232712
Restrictions
No unique index.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The DROP TABLE statement removes a table from the current database.
115364224029232561
Syntax
DROP TABLE
<base table name> [
CASCADE
RESTRICT
115364224029232562
Example
DROP TABLE NEWCUST
115364224029232563
Arguments
<base table name> is the name of the table to remove.
CASCADE removes any dependent objects. RESTRICT fails if there are dependent objects.
This operation removes a Notes form. It does not remove the documents created with that form. When Notes tries to display one of these documents, Notes displays an error message because it cannot find the form needed to display the document.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Click here
The Universal Relation
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The DROP VIEW statement removes a view from the current database.
115364224029232561
Syntax
DROP VIEW
<view name>
115364224029232562
Example
DROP VIEW NYCUST
115364224029232563
Arguments
<view name> is the name of the view to remove.
115364224029232564
Restrictions:
Dependent objects and documents are not removed.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The INSERT statement adds a row to a table. Values can be specified explicitly or derived from a query. The value assigned to a column must be compatible with the column data type. If no value is specified, a default is assigned if available.
INSERT INTO NEWSALTAB SELECT SALARY, LASTNAME FROM STAFF,SALES WHERE STAFF.STAFF_NO = SALES.STAFF_NO AND SALARY BETWEEN 5500 AND 6000
115364224029232563
Arguments
<table name> is the name of the table into which to insert data.
<column list> is an optional list of the columns to update. If you do not specify a column list, values are assigned to columns in the order in which they appear in the definition of the table. You must separate column names with commas and enclose the entire list in parentheses.
<value list> You must separate values with commas and enclose the entire list in parentheses. Each value must be one of the following:
A quoted string constant (use single quotes)
A numeric constant
A dynamic parameter
The INSERT statement must contain either a VALUES clause or a SELECT clause.
<query specification> is:
SELECT [ DISTINCT ] <select list>
FROM <table name>
[ WHERE <search condition> ]
[ GROUP BY <column list> ]
[ HAVING <search condition> ]
<select list> is { * | <expression list> }
<search condition> is described under WHERE.
115364224029232564
Notes Field Events
When the Notes driver inserts a new record into a table, it evaluates three formulas in the form design for every field (as Notes does when you compose a document):
Default Value
Input Translation
Input Validation
The field definition can include any of these formulas or none of them. These formulas are evaluated in the following order:
1. The Default Value formula (if it exists). This formula is evaluated to supply a value when the INSERT statement contains no data for the field.
2. The Input Translation formula.y
The Input Translation formula could change the value of the inserted data.
3. The Input Validation formula.
If the Input Validation formula returns FALSE, the insertion of that row fails. If you are inserting a single record and the insertion fails, SQLExecute and SQLExecDirect return SQL_ERROR. If you are inserting multiple records and one or more of the insertions fails an input validation check, the return code from SQLExecute or SQLExecDirect is SQL_SUCCESS_WITH_INFO. This tells the user what failed and allows valid insertions to continue. If all the insertions fail, SQLExecute and SQLExecDirect return SQL_ERROR.
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Click here
WHERE@
Click here
Click here
Data Types
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The GROUP BY clause groups the data from source tables by one or more columns
and produces a single summary row for each column name specified.
115304224029232561
Syntax
GROUP BY
<column list>
115304224029232562
Example
SELECT LOCATION, AVG(UNITCOST) FROM INVENTORY GROUP BY LOCATION
This list cannot include derived columns. All columns in the GROUP BY clause must also appear in the SELECT clause.
<expression list> is a list of column names and other expressions whose values will appear in the result table (result set).
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column Functions
Click here
Click here
Data Types
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
Column functions can be part of a SELECT clause. A column function takes an entire column of data as its argument and produces a single data item that summarizes the column. For example, the AVG column function takes a column of data and computes its average.
The argument to a column function can be a field name or an expression. NotesSQL supports the following Column functions:
Function
Description
Returns the average of the values in a numeric field or expression. For example, AVG(SALES) returns the average of all values in the "SALES" column.
COUNT
Returns the number of values in any field expression.
Returns the highest value in any field expression. For example, MAX(SALES) returns the highest SALES field value.
Returns the lowest value in any field expression. For example, MIN(SALES) returns the lowest SALES field value.
Returns the total of all values in a numeric field expression. For example, SUM(SALES) returns the sum of all SALES field values.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The FOR UPDATE clause allows the specification of a list of column names.
115414224029232561
Syntax
FOR UPDATE OF
<column list>
115414224029232562
Example
SELECT * FROM INVENTORY WHERE LOCATION='New York' FOR UPDATE OF ON_HAND
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
The FROM clause specifies the table names or views that are the source tables for a query.
115304224029232561
Syntax
{<table reference> [ , ... ]
115304224029232562
Example
SELECT * FROM CUSTOMER
115304224029232563
Arguments
<table reference> is one of the following:
The name of a table or view with an optional alias.
An ODBC-outer-join-extension. For example:
{ oj CUSTOMER LEFT OUTER JOIN ORDER ON CUSTOMER.CUSTNO = ORDER.CUSTNO }
An INNER JOIN with an ON clause. For example:
CUSTOMER INNER JOIN ORDER ON CUSTOMER.CUSTNO = ORDER.CUSTNO
(Crystal Reports users may experience problems with operations that use INNER JOINs. For more information, see "
Click here
Tips for Third-Party Applications and Caveats
Click here
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
Architecture, application/driverOverviewSecurity
Driver, adding and deletingInstallation
Capability reportingFunctionsODBCSQLGetInfo
Driver Capabilities: SQLGetInfo Return Values
ODBCSQL, grammar supported
Summary of Supported ODBC SQL Grammar
FoA"g
ODBCSQL, grammar supported
Column namesFormsIndexesTablesViews
Mapping SQL Tables, Views, and Indexes to and from Notes
These column expressions are generally qualified by a table name or alias. Column expressions cannot include asterisks ( * ).
See related topics
Click here
SQL Typographical Conventions
Click here
Click here
Column, Index, Table, and View Names
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL supports the following predicate operators:
Operator
Meaning
Less Than
Greater Than
Less Than or Equal To
Greater Than or Equal To
Equal To
Not Equal To
BETWEEN
Specifies a range of values between a lower and upper boundary.
Specifies a member of a set of specified values or a member of a subquery.
Use for matching a pattern. Wildcard characters in LIKE predicate:
Use an underscore ( _ ) to represent a single character.
Use a percent symbol ( % ) to represent any number of characters.p
Use a backslash ( \ ) as the escape character.
Use the NOT operator with another operator to specify a search condition that is false. For example: NOT IN, NOT LIKE, or NOT BETWEEN.
Use to compare a value to each value returned by a subquery. Can be prefaced by =, <>, >, >=, <, <=, or =.
=ANY is equivalent to IN.
<>ANY is equivalent to NOT IN.
SOME is an alternate keyword for ANY.
Use to compare a value to each value returned by a subquery. Can be prefaced by =, <>, >, >=, <, or <=.
EXISTS
"True" if a subquery returned at least one record.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
ODBC (Open Database Connectivity) is an interface that allows applications to retrieve and manipulate data in database management systems using SQL. The interface allows a single application to connect to many different types of databases through a standard protocol. ODBC is implemented as a driver manager and multiple drivers. Each driver links the application to a specific type of database.
The major components of ODBC are the ODBC driver manager and the underlying drivers. Each ODBC driver is a dynamic link library for accessing a specific type of database. ODBC uses the driver manager to handle finding and connecting to a driver and an administration tool to configure ODBC data sources. An ODBC data source may be a local directory or file or a combination of a DBMS, a remote host operating system, and a network.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
NotesSQL is the Lotus Notes ODBC (2.x) driver. It allows you to open and query a Notes database (an .NSF file) through the
Click here
Open Database Connectivity (ODBC) interface
Click here
Here is the application/driver hierarchy (using 32-bit Windows file names):
(((
```HHH
ppp000
CN=Michael Stewa
*On 32-bit Windows, NotesSQL consists of 3 DLLs. When an ODBC connection is made, NSQL32.DLL is called by ODBC32.DLL, the ODBC Driver Manager. NSQL32.DLL is the DLL whose name appears in ODBC.INI. NSQLV32.DLL is called by NSQL32.DLL. When configuring a NotesSQL data source (an operation not shown in the diagram), NSQLC32.DLL is called by the ODBC Administrator.
** NotesSQL communicates exclusively with the Lotus Notes client or Domino server software. Lotus Notes and Domino handle all network traffic and security.
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
Before you can connect to a Notes database with NotesSQL, you must add a data source for it. You can change or delete a data source at any time. You can use as many data sources as you like with a particular driver, provided no two data sources have the same name.
368837628829232348
To add a Lotus Notes data source:
1.
Start the ODBC Data Source Administrator.
2. Select the type of data source you want to add. On 32-bit Windows platforms, you can add a user, system, or file data source.
3. Click Add.
4. In the Create New Data Source wizard, select Lotus NotesSQL from the list of installed ODBC drivers. (If you are adding a file data source, you can click the Advanced button to manually add driver-specific keywords.)
5. Click Finish if you are adding a user or system data source. Click Next if you are adding a file data source.
If you are adding a user or system data source, the Lotus Notes ODBC 2.0 Setup dialog box appears:
If you are adding a file data source, successive Create New Data Source screens prompt you for the data source name and show you the information that will be included in the .DSN data source file. If NotesSQL requires more information (such as a database name) to establish a connection, the NotesSQL Connect dialog box appears:
om Folder
The Lotus Notes ODBC 2.0 Setup and NotesSQL Connect dialog boxes are very similar. Each lets you specify a server and database for the data source and set NotesSQL options. The Lotus Notes ODBC 2.0 Setup dialog box also lets you specify the data source name and description.
6. To specify the server and database, do one of the following:
Type the server and/or database name in the Server and Database fields. If you do not specify a server name, the local machine is assumed. If the database is not in the Notes data directory, specify an absolute path or a path relative to that directory. You can use a Universal Naming Convention (UNC) file name if the database is on a local server (a server accessible through your network file system, not a Domino server). UNC file names for Notes databases have the form "\\<ServerName>\<ShareName>\<Directory1>\<Directory2>\<...>\<DatabaseName>.NSF".
Choose an available server and/or database from the lists in the appropriate fields. (Note that, depending on the number of servers and databases in your system, it may take some time to build these lists.)
If you specify the local machine (or leave the Server field blank), click Browse to search for available databases on your local machine.
If you are creating a machine (user or system) data source, you do not need to have Notes loaded. If Notes is not loaded, the browse button and drop-down lists will not be available, but you can still type server and database names in the appropriate edit fields.
7. Set NotesSQL options as needed. (For more information about the NotesSQL options in these dialog boxes, see "
Click here
Configuring a Lotus Notes Data Source
Click here
8. When you have finished defining your data source, click OK.
If you are using 16-bit Windows, you cannot add a file data source. Also note that the ODBC Data Source Administrator and NotesSQL will present slightly different dialog boxes. For example, the Browse button and drop-down server and database lists do not appear in the NotesSQL dialog boxes for 16-bit Windows. However, the basic method for adding data sources is similar to the method described above.
See related topics
Click here
Configuring a Lotus Notes Data Source
Click here
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
You can use NotesSQL with any application that supports ODBC drivers for external data access. To connect to the Notes database, you select the data source you defined through the ODBC Data Source Administrator. (For more information about defining data sources, see "
Click here
Adding a Lotus Notes Data Source
Click here
Click here
Modifying a Lotus Notes Data Source
Click here
," and "
Click here
Configuring a Lotus Notes Data Source
Click here
If your Notes user ID is password-protected, Notes prompts for a password when you first connect to a remote database using NotesSQL. This information is not database-specific and is retained for the length of the session, so you will not need to enter it more than once if you connect to more than one database.
Lotus Notes never prompts for a user ID. Your user ID is part of your workstation installation.
73957251229232354
Connection Keywords and Strings
Whether you are using a third-party ODBC-compliant application or creating your own program, one way to connect to a data source is to call the ODBC API function SQLDriverConnect. Depending on the method you use, you may either call the function explicitly or have it called for you by an application or other software layer (such as ADO).
When you call SQLDriverConnect, you pass keyword/value pairs as arguments. NotesSQL supports the following general keywords for the SQLDriverConnect call:
Keyword
Description
The name of the data source.
Database
The name of the Notes database, with a path if necessary.
Server
The name of the Notes server where the database is located.
If the database is on the local workstation, leave this field blank.
DRIVER
The name of the ODBC driver.
If you use the DSN keyword, that is the only keyword required. If you do not use the DSN keyword, only the Database and DRIVER keywords are required. If you do not specify a server, the local machine is assumed.
To connect to the Personnel data source, you would use the following connection string:
DSN=Personnel;
To connect to a database in the directory PERSONNEL on server HR_1, you would use the following connection string:
NotesSQL also supports the following driver-specific keywords:
Keyword
Minimum
Maximum
Defaultd
f$Z'U
Description
MaxSubquery
f$Z'U
This parameter specifies the maximum number of nested subqueries in a SQL statement.
MaxStmtLen
32767
f$Z'U
This parameter specifies the maximum length of a SQL statement passed to prepare, execute, executeQuery, or executeUpdate.
MaxRels
f$Z'U
This parameter specifies the maximum number of tables in a single SQL statement.
MaxVarcharLen
15360
f$Z'U
This parameter specifies the maximum number of bytes NotesSQL allows in a string associated with a text field. This limits the number of characters returned from a Notes text field and the length of a string to be inserted into a Notes text field.
MaxLongVarcharLen
15360
f$Z'U
This parameter is similar to MaxVarcharLen, but it applies to rich text fields.
KeepTempIdx
f$Z'U
This option controls the saving of temporary indexes. Set to 1, temporary indexes will be saved until disconnect() is called. Set to 0, temporary indexes will be deleted at the end of each SQL statement.
ShowImplicitFlds
f$Z'U
This option controls whether certain Notes fields are provided through DatabaseMetaData. Set to 0, internal fields will not be shown. Set to 1, internal fields will be shown.
MapSpecialChars
f$Z'U
This option controls whether certain characters are mapped to the underscore character ( _ ). Set to 0, no mapping will occur. Set to 1, special characters will be mapped to the underscore.
ThreadTimeout
99,999
f$Z'U
All calls are serialized so that they proceed one after the other. This parameter specifies the number of seconds one thread should wait for another thread to finish before timing out. Note that a value of 0 means wait forever.
Each keyword has a minimum, maximum, and default value. If you do not specify a value, the default value is used.
These keywords correspond to the options you can set in NotesSQl dialog boxes when adding or modifying a data source. For more information, see "@
Click here
Configuring a Lotus Notes Data SourceC
Click here
SQLDriverConnect returns an output string containing the keyword/value pairs you specified. We recommend that you specify a 512 byte buffer for the output string.
See related topics
Click here
Adding a Lotus Notes Data Source
Click here
Click here
Configuring a Lotus Notes Data Source
Click here
CREATE TABLESQL, grammar supported
Universal Relation
Universal Relation
FormsIndexesPerformanceTablesViews
Using SQL Tables Derived from Notes Forms and Views
FAQFile DSNsJDBCJava toolsJoinsNotes, versions supportedODBCReports, creatingSecurityTools, using with NotesSQL
Contents
OverviewContext Help FormFrequently Asked Questions (FAQ)Overview
H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_74017251229232361=Which versions of Notes can NotesSQL 2.05 access?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_205816464029255036=<+!><-!>Which Notes client software must I install to use NotesSQL 2.05 access?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254867=Can I use NotesSQL 2.05 with a File DSN?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254868=How can I improve the performance of my NotesSQL application?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254869=Can I use a Universal Naming Convention (UNC) file name to connect to a Notes database?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254871=How can a third party application access NotesSQL 2.05 internal fields?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254870=Can I access the UNID (Universal Note ID) and the $REF fields of a Notes document? H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254872=Can I use internal fields in a join?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_257363766429254873=What changes were made in NotesSQL 2.05 to improve the NotesSQL ODBC Setup?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_16971878429254897=Which platforms does NotesSQL 2.05 support?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_16971878429254898=Can I use NotesSQL 2.05 on a Domino server without installing a Notes client?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232354=Can I share ODBC handles between threads?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232355=Can I use NotesSQL with the JDBC-ODBC bridge?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232356=What Java tools can I use to connect to NotesSQL using the JDBC-ODBC bridge?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_205816464029255037=<+!><-!>How can I use NotesSQL with eSuite?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232357=Can I use NotesSQL with NetObjects Fusion?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232358=Can I access NotesSQL using Active Server Pages? H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232359=Can I use FrontPage 98 to generate Active Server Pages (ASP) and access NotesSQL 2.05?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_73957251229232360=Where can I get NotesSQL 2.05?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_74017251229232362=Can I produce a report that joins Notes data and data from my relational database?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_74017251229232363=Can I update Notes databases via NotesSQL 2.05?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_74017251229232364=What security is in place when I use NotesSQL 2.05 to access Notes?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_423141404829251039=The NotesSQL installation process seems different. What has changed?H_FREQUENTLY_ASKED_QUESTIONS_FAQ_OVERVIEW_MIDTOPIC_423141404829251038=How can I contact the NotesSQL team regarding bugs, questions, or feedback?
CN=Michael Stewart/OU=CAM/O=Lotus
###############################################
H_UPDATE_POSITIONED_OVERVIEWUPDATE Positioned
05 Appendix A: SQL Statements
SQL, grammar supportedUPDATE Positioned
Contents
OverviewContext Help FormUPDATE PositionedOverview
a. How Do I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
Use DatabasesUse ViewsUse DocumentsSearch for InformationUse MailPrint Documents and ViewsKeep Notes SecureShare Information With Other ApplicationsUse Notes Away from a NetworkDesign Notes DatabasesManage Notes DatabasesAdminisqer Notes Servers---Where is this Release 3 command?What are the important new features?What changed at the last minute?How do I upgrade my site to Release 4?----Things you do before startingThings you do every dayThings you do frequentlyOther things-----About NotesSetting Up NotesStarting & Stopping NotesAbout Other Notes Documentation------About MenusAbout the SmartIcons PaletteAbout WindowsAbout FoldersAbout Split PanesAbout the Status Bar-------SmartIcon ShortcutsMouse ShortcutsKeyboard Shortcuts-------!About ScriptingLotusScript BasicsLotusScript Reference----------@Functions Basics@Functions Reference@Commands Basics@Comands Reference---------About MessagesAbout Customer SupportList of Messagest
a. How Ao I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
a. How Do I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
a. How Do I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
Mapping SQL Tables, Views, and Indexes to and from Notes
Mapping Notes Names to!SQL Names
Using the Universal Relation
Using SQL Tables Derived from Notes Forms and Views
Example: Using SQL Tables Derived from Notes Forms and Views
Using Notes Views as Indexes
Using Notes Internal Fields
Tips for Third-Party Applications and Caveats
Frequently Asked Questions (FAQ)
Driver Capabilities: SQLGetInfo Return Values
Summary of Supported ODBC SQL Grammar
Exceptions to ODBC!SQL Grammar
SQL Typographical Conventions
Column, Index, Table, and View Names
Multi-Valued Fields (List Fields)
00 Chapter 0: How to Use This Guide
01 Chapter 1: About Lotus NotesSQL
01 Chapter 1: About Lotus NotesSQL
New Features in this Release:
01 Chapter 1: About Lotus NotesSQL
!UpXX
01 Chapter 1: About Lotus NotesSQL
01 Chapter 1: About Lotus NotesSQL
01 Chapter 1: About Lotus NotesSQL
01 Chapter 1: About Lotus NotesSQL
01 Chapter 1: About Lotus NotesSQL
New Features in this Release:
01 Chapter 1: About Lotus NotesSQL
!UpXX
00 Chapter 0: How to Use This Guide
Chapter 0: How to Use This Guide
01 Chapter 1: About Lotus NotesSQL
Chapter 1: About Lotus NotesSQL
02 Chapter 2: Installatian and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuqation
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
Adding a Lotus Notes Data Sourcep
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
! Adding a Lotus Notes Data Sourcep
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
Configuring a Lotus Notes Data Source
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
Modifying a Lotus Notes Data Source
02 Chapter 2: Installation and Configuraqion
Chapter 2: Installation and Configuration
Deleting a Lotus Notes Data Sourcep
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
02 Chapter 2: Installation and Configuration
Chapter 2: Installation and Configuration
Summary of Supported ODBC SQL Grammar
FoA"g
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
Column, Index, Table, and View Names
Multi-Valued Fields (List Fields)
]L>7a
sJ=:'g
Summary of Supported ODBC SQL Grammar
FoA"g
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
Column, Index, Table, and View Names
Multi-Valued Fields (List Fields)
]L>7a
Summary of Supported ODBC SQL Grammar
Exceptions to ODBC SQL Grammar
SQL Typographical Conventions
Column, Index, Table, and View Names
Multi-Valued Fields (List Fields)
&Arial
Baskerville
&CommonBullets
5Courier New
1Fixedsys
&Impact
"MS Sans Serif
Symbol
"Small Fonts
"System
1Terminal
Times New Roman
Wingdings
"Univers
Rockwell Light
FPalace Script
You might need to modify a data source configuration, perhaps because you have moved a database.
368837628829232348
To modify a Lotus Notes data source:
1.
Start the ODBC Data Source Administrator.
2. Select the type of data source you want to modify. On 32-bit Windows platforms, you can modify user, system, or file data sources.
3. Select the data source in the Data Sources list box.
4. Click Configure.
If you are modifying a user or system data source, the Lotus Notes ODBC 2.0 Setup dialog box appears:
Rockwell L
cript
68837628829232$F
If you are modifying a file data source, the NotesSQL Connect dialog box appears:
Rockwell L
cript
68837628829232$F
The Lotus Notes ODBC 2.0 Setup and NotesSQL Connect dialog boxes are very similar. Each lets you specify a server and database for the data source and set NotesSQL options. The Lotus Notes ODBC 2.0 Setup dialog box also lets you specify the data source name and description.
5. To specify the server and database, do one of the following:
Type the server and/or database name in the Server and Database fields. If you do not specify a server name, the local machine is assumed. If the database is not in the Notes data directory, specify an absolute path or a path relative to that directory.
Choose an available server and/or database from the lists in the appropriate fields. (Note that, depending on the number of servers and databases in your system, it may take some time to build these lists.)
If you specify the local machine (or leave the Server field blank), click Browse to search for available databases on your local machine.
If you are modifying a machine (user or system) data source, you do not need to have Notes loaded in order to specify a server and database. If Notes is not loaded, the browse button and drop-down lists will not be available, but you can still type server and database names in the appropriate edit fields.
6. Set NotesSQL options as needed. (For more information about the NotesSQL options in these dialog boxes, see "
Click here
Configuring a Lotus Notes Data Source
Click here
7. When you're finished modifying your data source, click OK.
If you are using 16-bit Windows, you cannot modify a file data source. Also note that the ODBC Data Source Administrator and NotesSQL will present slightly different dialog boxes. For example, the Browse button and drop-down server and database lists do not appear in the NotesSQL dialog boxes for 16-bit Windows. However, the basic method is similar to the method described above.
See related topics
Click here
Adding a Lotus Notes Data Source
Click here
Click here
Configuring a Lotus Notes Data Source
Click here
Click here
Deleting a Lotus Notes Data Source
Click here
of the following:
DROP INDEXSQL, grammar supported
Data sourceDatabase connection parametersInstallationSQL statement length
Configuring a Lotus Notes Data Source
Data source
Data source
Modifying a Lotus Notes Data Source
Data source
Deleting a Lotus Notes Data Source
FAQFile DSNsJDBCJava toolsJoinsNotes, versions supportedODBCReports, creatingSecurityTools, using with NotesSQL
Fields, Notes internal
Internal fields, Notes
Notes internal fields
New features
Overview
Data source
Documentation, about
Uninstalling NotesSQL
Connection string
Keywords
Notes names, mapping to SQL
SQL names, mapping from Notes
Forms
Performance
Views
View column definitions
Hardware, required
Prerequisites
Requirements, hardware and software
Software, required
Error messages
Architecture, application/driver
Security
Driver, adding and deleting
Installation
Capability reporting
Functions
SQLGetInfo
SQL, grammar supported
Universal Relation
Column names
Indexes
Tables
Data types
Fields, rich text
Rich text fields
CREATE INDEX
CREATE TABLE
CREATE VIEW
DELETE Positioned
DELETE Searched
Notes views
DROP VIEW
INSERT
SELECT
Applications, tips
Tips, applications
Numeric operators
Operators, numeric
List fields
Multi-valued fields
HAVING
ORDER BY
UNION
WHERE
Operators, predicate
Predicate operators
Programming
SQLCancel
SQLColumnPrivileges
SQLColumns
SQLForeignKeys
SQLGetConnectOption
SQLGetStmtOption
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLSetConnectOption
SQLSetParam
SQLSetScrollOptions
SQLSetStmtOption
SQLTablePrivileges
SQLTables
SQLTransact
ALTER TABLE
DROP INDEX
DROP TABLE
Database connection parameters
SQL statement length
UPDATE Searched
GROUP BY
Column functions
Functions, column
FOR UPDATE
FAQFile DSNsJDBCJava toolsJoinsNotes, versions supportedODBCReports, creatingSecurityTools, using with NotesSQL
F\FAQF\File DSNsJ\JDBCJ\Java toolsJ\JoinsN\Notes, versions supportedO\ODBCR\Reports, creatingS\SecurityT\Tools, using with NotesSQL
S\SQL, grammar supportedU\UPDATE Positioned
Column, Index, Table, and View Names
Uninstalling NotesSQL
Universal Relation
UNION
UPDATE Searched
UPDATE Positioned
@N4GP
ViewSection
ViewSectionAssign this topic to a topic group.3
Use DatabasesUse ViewsUse DocumentsSearch for InformationUse MailPrint Documents and ViewsKeep Notes SecureShare Information With Other ApplicationsUse Notes Away from a NetworkDesign Notes DatabasesManage Notes DatabasesAdminisqer Notes Servers---Where is this Release 3 command?What are the important new features?What changed at the last minute?How do I upgrade my site to Release 4?----Things you do before startingThings you do every dayThings you do frequentlyOther things-----About NotesSetting Up NotesStarting & Stopping NotesAbout Other Notes Documentation------About MenusAbout the SmartIcons PaletteAbout WindowsAbout FoldersAbout Split PanesAbout the Status Bar-------SmartIcon ShortcutsMouse ShortcutsKeyboard Shortcuts-------!About ScriptingLotusScript BasicsLotusScript Reference----------@Functions Basics@Functions Reference@Commands Basics@Comands Reference---------About MessagesAbout Customer SupportList of Messagest
a. How Ao I?b. What's New?c. Top 10 Taskse. Search IndexRef a. Setting UpRef b. WorkspaceRef c. ShortcutsRef d. ScriptingRef e. @FunctionsRef f. Troubleshooting.
(.txt)